
--check for existant foreign key
----works
if exists (select * from sys.foreign_keys where object_id = OBJECT_ID(N'fk_works_users') and parent_object_id = OBJECT_ID(N'works'))
alter table works drop constraint fk_works_users
go
if exists (select * from sys.foreign_keys where object_id = OBJECT_ID(N'fk_works_school') and parent_object_id = OBJECT_ID(N'works'))
alter table works drop constraint fk_works_school
go
if exists (select * from sys.foreign_keys where object_id = OBJECT_ID(N'fk_works_faculty') and parent_object_id = OBJECT_ID(N'works'))
alter table works drop constraint fk_works_faculty
go
----assignments
if exists (select * from sys.foreign_keys where object_id = OBJECT_ID(N'fk_assignments_events') and parent_object_id = OBJECT_ID(N'assignments'))
alter table assignments drop constraint fk_assignments_events
go
if exists (select * from sys.foreign_keys where object_id = OBJECT_ID(N'fk_assignments_users') and parent_object_id = OBJECT_ID(N'assignments'))
alter table assignments drop constraint fk_assignments_users
go
----videos
if exists (select * from sys.foreign_keys where object_id = OBJECT_ID(N'fk_videos_users') and parent_object_id = OBJECT_ID(N'videos'))
alter table videos drop constraint fk_videos_users
go
----photos
if exists (select * from sys.foreign_keys where object_id = OBJECT_ID(N'fk_photos_users') and parent_object_id = OBJECT_ID(N'photos'))
alter table photos drop constraint fk_photos_users
go
----courseevent
if exists (select * from sys.foreign_keys where object_id = OBJECT_ID(N'fk_courseevent_course') and parent_object_id = OBJECT_ID(N'courseevent'))
alter table courseevent drop constraint fk_courseevent_course
go
----coursemembers
if exists (select * from sys.foreign_keys where object_id = OBJECT_ID(N'fk_coursemembers_course') and parent_object_id = OBJECT_ID(N'coursemembers'))
alter table coursemembers drop constraint fk_coursemembers_course
go
if exists (select * from sys.foreign_keys where object_id = OBJECT_ID(N'fk_coursemembers_users') and parent_object_id = OBJECT_ID(N'coursemembers'))
alter table coursemembers drop constraint fk_coursemembers_users
go
----course
if exists ( select * from sys.foreign_keys where object_id = OBJECT_ID(N'fk_course_school') and parent_object_id = OBJECT_ID(N'course'))
alter table course drop constraint fk_course_school
go
if exists (select * from sys.foreign_keys where object_id = OBJECT_ID(N'fk_course_faculty') and parent_object_id = OBJECT_ID(N'course'))
alter table course drop constraint fk_course_faculty
go
if exists (select * from sys.foreign_keys where object_id = OBJECT_ID(N'fk_course_users') and parent_object_id = OBJECT_ID(N'course'))
alter table course drop constraint fk_course_users
go
----relationship
if exists (select * from sys.foreign_keys where object_id = OBJECT_ID(N'fk_relationship_users1') and parent_object_id = OBJECT_ID(N'relationship'))
alter table relationship drop constraint fk_relationship_users1
go
if exists (select * from sys.foreign_keys where object_id = OBJECT_ID(N'fk_relationship_users2') and parent_object_id = OBJECT_ID(N'relationship'))
alter table relationship drop constraint fk_relationship_users2
go
----group_g
if exists (select * from sys.foreign_keys where object_id = OBJECT_ID(N'fk_group_g_users') and parent_object_id = OBJECT_ID(N'group_g'))
alter table group_g drop constraint fk_group_g_users
go
----groupmembers
if exists (select * from sys.foreign_keys where object_id = OBJECT_ID(N'fk_groupmembers_group_g') and parent_object_id = OBJECT_ID(N'groupmembers'))
alter table groupmembers drop constraint fk_groupmembers_group_g
go
if exists (select * from sys.foreign_keys where object_id = OBJECT_ID(N'fk_groupmembers_users') and parent_object_id = OBJECT_ID(N'groupmembers'))
alter table groupmembers drop constraint fk_groupmembers_users
go
----comment
if exists (select * from sys.foreign_keys where object_id = OBJECT_ID(N'fk_comment_topic') and parent_object_id = OBJECT_ID(N'comment'))
alter table comment drop constraint fk_comment_topic
go
if exists (select * from sys.foreign_keys where object_id = OBJECT_ID(N'fk_comment_users') and parent_object_id = OBJECT_ID(N'comment'))
alter table comment drop constraint fk_comment_users
go
----topic
if exists (select * from sys.foreign_keys where object_id = OBJECT_ID(N'fk_topic_users') and parent_object_id = OBJECT_ID(N'topic'))
alter table topic drop constraint fk_topic_users
go
if exists (select * from sys.foreign_keys where object_id = OBJECT_ID(N'fk_topic_users1') and parent_object_id = OBJECT_ID(N'topic'))
alter table topic drop constraint fk_topic_users1
go
if exists (select * from sys.foreign_keys where object_id = OBJECT_ID(N'fk_topic_group') and parent_object_id = OBJECT_ID(N'topic'))
alter table topic drop constraint fk_topic_group
go
if exists (select * from sys.foreign_keys where object_id = OBJECT_ID(N'fk_topic_course') and parent_object_id = OBJECT_ID(N'topic'))
alter table topic drop constraint fk_topic_course
go
----like_l
if exists (select * from sys.foreign_keys where object_id = OBJECT_ID(N'fk_like_l_users') and parent_object_id = OBJECT_ID(N'like_l'))
alter table like_l drop constraint fk_like_l_users
go
if exists (select * from sys.foreign_keys where object_id = OBJECT_ID(N'fk_like_l_topic') and parent_object_id = OBJECT_ID(N'like_l'))
alter table like_l drop constraint fk_like_l_topic
go

--check for existant table
----school
if OBJECT_ID('school') is not null drop table school
go
----faculty
if OBJECT_ID('faculty') is not null drop table faculty
go
----course
if OBJECT_ID('course') is not null drop table course
go
----coursemembers
if OBJECT_ID('coursemembers') is not null drop table coursemembers
go
----courseevent
if OBJECT_ID('courseevent') is not null drop table courseevent
go
----relationship
if OBJECT_ID('relationship') is not null drop table relationship
go
----users
if OBJECT_ID('users') is not null drop table users
go
----photos
if OBJECT_ID('photos') is not null drop table photos
go
----videos
if OBJECT_ID('videos') is not null drop table videos
go
----assignments
if OBJECT_ID('assignments') is not null drop table assignments
go
----works
if OBJECT_ID('works') is not null drop table works
go
----group_g
if OBJECT_ID ('group_g') is not null drop table group_g
go
----groupmembers
if OBJECT_ID ('groupmembers') is not null drop table groupmembers
go
----topic
if OBJECT_ID ('topic') is not null drop table topic
go
----comment
if OBJECT_ID ('comment') is not null drop table comment
go 
----like_l
if OBJECT_ID ('like_l') is not null drop table like_l
go

--Create table
----school
create table school(
	id bigint not null,
	name nvarchar(255) not null,
	constraint pk_school primary key (id)
)

go

----facuty
create table faculty(
	id bigint not null,
	name nvarchar(255) not null,
	constraint pk_faculty primary key (id)
)

go

----users
create table users(
	id bigint not null,
	username nvarchar(255) not null,
	password_p nvarchar(255) not null,
	emai nvarchar(255) not null,
	firstname nvarchar(255) null,
	lastname nvarchar(255) null,
	gender bit null,
	birthday timestamp null,
	confirmed nvarchar(255) not null,
	constraint pk_users primary key (id)
)

go

----course
create table course(
	id bigint not null,
	name nvarchar(255) not null,
	faculty_id bigint not null,
	school_id bigint not null,
	manager bigint not null,
	startdate datetime not null,
	coursedescription nvarchar(1000),
	constraint pk_course primary key (id),
	constraint fk_course_school foreign key (school_id) references school(id),
	constraint fk_course_faculty foreign key (faculty_id) references faculty(id),
	constraint fk_course_users foreign key (manager) references users(id)
)

go

----coursemembers
create table coursemembers(
	course_id bigint not null,
	users_id bigint not null,
	usertype tinyint not null,
	constraint pk_coursemembers primary key (course_id, users_id),
	constraint fk_coursemembers_course foreign key (course_id) references course(id),
	constraint fk_coursemembers_users foreign key (users_id) references users(id)
)

go

----event
create table courseevent(
	id bigint not null,
	course_id bigint not null,
	name nvarchar(255) not null,
	eventdescription nvarchar(1000),
	startdate datetime not null,
	duedate datetime not null,
	deadline datetime not null,
	link nvarchar(1000) not null,
	constraint pk_courseevent primary key (id),
	constraint fk_courseevent_course foreign key (course_id) references course(id)
)

go

----relationship
create table relationship(
	usera_id bigint not null,
	userb_id bigint not null,
	constraint pk_relationship primary key (usera_id, userb_id),
	constraint fk_relationship_users1 foreign key (usera_id) references users(id)
)



go

----photos
create table photos(
	id bigint not null,
	name nvarchar(255) not null,
	path_p nvarchar(255) not null,
	datecreated timestamp not null,
	size int not null,
	userid bigint not null,
	photosdescription nvarchar(255) null,
	constraint pk_photos primary key (id),
	constraint fk_photos_users foreign key (userid) references users(id)
)

go

----videos
create table videos(
	id bigint not null,
	name nvarchar(255) not null,
	path_p nvarchar(255) not null,
	datecreated timestamp not null,
	size int not null,
	userid bigint not null,
	videosdescription nvarchar(255) null,
	constraint pk_videos primary key (id),
	constraint fk_videos_users foreign key (userid) references users(id)
)

go

----assignments
create table assignments(
	eventid bigint not null,
	userid bigint not null,
	constraint pk_assignments primary key (eventid, userid),
	constraint fk_assignments_events foreign key (eventid) references courseevent(id),
	constraint fk_assignments_users foreign key (userid) references users(id)
)

go

----works
create table works(
	id bigint not null,
	userid bigint not null,
	position nvarchar(255) not null,
	worksdescription nvarchar(1000),
	startdate datetime not null,
	universityid bigint not null,
	facultyid bigint not null,
	email varchar(255) not null,
	constraint pk_works primary key (id),
	constraint fk_works_users foreign key (userid ) references users(id),
	constraint fk_works_school foreign key (universityid) references school(id),
	constraint fk_works_faculty foreign key (facultyid) references faculty(id)
)

go

----group_g
create table group_g(
	id bigint not null,
	name nvarchar(150) not null,
	groupdescription nvarchar (1000) not null,
	stardate datetime not null,
	admin_a bigint not null,
	constraint pk_group_g primary key (id),
	constraint fk_group_g_users foreign key (admin_a) references users(id),
)

go

----groupmembers
create table groupmembers(
	group_g_id bigint not null,
	users_id bigint not null,
	constraint pk_groupmembers primary key (group_g_id, users_id),
	constraint fk_groupmembers_group_g foreign key (group_g_id) references group_g(id),
	constraint fk_groupmembers_users foreign key (users_id) references users(id)
)

go

----topic
create table topic(
	id bigint not null,
	users_id bigint not null,
	home_id bigint not null,
	type_t tinyint not null,
	topiccontent nvarchar(1000) not null,
	link nvarchar(1000),
	likenum int,
	commentnum int,
	time_t datetime not null,
	constraint pk_topic primary key (id),
	constraint fk_topic_users foreign key (users_id) references users(id),
	constraint fk_topic_users1 foreign key (home_id) references users(id),
	constraint fk_topic_group foreign key (home_id) references group_g(id),
	constraint fk_topic_course foreign key (home_id) references course(id)
)

go

----comment
create table comment(
	id bigint not null,
	users_id bigint not null,
	topic_id bigint not null,
	link nvarchar(1000),
	likenum int,
	commentcontent nvarchar(1000) not null,
	time_t datetime not null,
	constraint pk_comment primary key (id),
	constraint fk_comment_topic foreign key (topic_id) references topic(id),
	constraint fk_comment_users foreign key (users_id) references users(id)
)

go

----like_l
create table like_l(
	users_id bigint not null,
	post_id bigint not null,
	type_t tinyint not null,
	constraint pk_like_l primary key (users_id, post_id),
	constraint fk_like_l_users foreign key (users_id) references users(id),
	constraint fk_like_l_topic foreign key (post_id) references topic(id),
)